# Built-in Imports
import pathlib
import statistics as stats
from datetime import datetime, date

# Third Party Imports
import numpy as np
import pandas as pd
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import xlsxwriter

# Function I use often to speed up work on large local datasets. Overkill for this example, but adding for common workflow demonstration.
def get_and_pickle_data(xl_path, pkl_path, force_rerun=False):
    
    # Check for data already already processed, and that a forced rerun is not desired. If both, read from here.
    if (pkl_path.exists()) & (force_rerun == False):
        df = pd.read_pickle(pkl_path)
    else:
    # If processed file did not exist or rerun desired, read from raw excel file.  
        df = pd.read_excel(xl_path, parse_dates=True)
        df.to_pickle(pkl_path)
    return df

invoices_raw_path = pathlib.Path('../data/raw/falsesouth_receivables.xlsx')
invoices_pkl_path = pathlib.Path('../data/processed/falsesouth_receivables.pkl')

invoice_df = get_and_pickle_data(invoices_raw_path, invoices_pkl_path)

# Convert invoice dataframe datetime columns to date. Timestamp is not useful, and detracts from reporting later.
invoice_df['DUE_DATE'] = pd.to_datetime(invoice_df['DUE_DATE']).dt.date
invoice_df['INVOICE_DATE'] = pd.to_datetime(invoice_df['INVOICE_DATE']).dt.date

# Function to get total invoiced
def get_total_invoiced(df):
    total_invoiced = df['TOTAL_AMOUNT'].sum()
    return total_invoiced

# Function to get total outstanding
def get_total_outstanding(df):
    total_outstanding = df['BALANCE'].sum()
    return total_outstanding

# Function to get total BALANCE, where DUE_DATE is less than current datetime (Max date was 3/20/2022, so all are overdue)
def get_overdue_outstanding(df):
    overdue_df = df[df['DUE_DATE'] < datetime.now().date()]
    overdue_outstanding = overdue_df['BALANCE'].sum()
    return overdue_outstanding                          

# Function to resample/aggregate dataset to monthly totals by INVOICE_DATE month.
def get_monthly_totals(df):
    monthly_df = df.copy()
    monthly_df['INVOICE_DATE'] = pd.to_datetime(monthly_df['INVOICE_DATE'])
    monthly_df = monthly_df[['INVOICE_DATE', 'BALANCE', 'TOTAL_AMOUNT']].set_index('INVOICE_DATE').resample('BMS').sum()
    monthly_df['COLLECTED'] = monthly_df['TOTAL_AMOUNT'] - monthly_df['BALANCE']
    return monthly_df

monthly_totals = get_monthly_totals(invoice_df)

def get_invoice_counts(df):
    inv_counts_df = pd.DataFrame(df.groupby('COMPANY_NAME')['INVOICE_ID'].count()
                                ).reset_index().rename(columns={'INVOICE_ID': 'INVOICE_COUNT'})
    return inv_counts_df

def get_broker_invoices(df, broker_name):
    broker_items_df = df[df['COMPANY_NAME'] == broker_name]
    return broker_items_df

def write_broker_reports(df):
    '''
    Function to auto-generate broker statements.
    '''
    
    # Get a sorted list of unique broker company names to iterate through
    brokers = sorted(list(df.COMPANY_NAME.unique()))

    # Iterate through brokers, pulling statement line items, and writing each to a separate Excel report
    for broker in brokers:
        # Get broker line items
        broker_df = get_broker_invoices(df, broker)
        broker_df = broker_df.sort_values(by='DUE_DATE', ascending=False)
        broker_past_due = broker_df[(broker_df['DUE_DATE'] < datetime.now().date()) & (broker_df['BALANCE'] > 0)]
        broker_current = broker_df[broker_df['BALANCE'] == 0]
        
        # Establish path and file name
        statement_path = pathlib.Path('../statements/' + broker + ' - Statement.xlsx')
        
        # Instantiate xlsxwriter objects and variables to manipulate report.
        sheetname = 'Statement'
        writer = pd.ExcelWriter(statement_path, engine='xlsxwriter')
        workbook = writer.book
        start_row = 4
        len_past_due = len(broker_past_due)
        
        # If there are overdue invoices, write overdue line items first and then write historical paid items below.
        if len(broker_past_due) > 0:
            broker_past_due.to_excel(writer, sheet_name=sheetname, startrow=start_row, index=False)
            worksheet = writer.sheets[sheetname]
            broker_current.to_excel(writer, sheet_name=sheetname, startrow=start_row + len_past_due + start_row, index=False)
            worksheet.write(0, 0, 'FalseSouth - Brokerage Account Statement', workbook.add_format({'bold': True, 'color': '#1d9385', 'size': 28}))
            worksheet.write(1, 0, 'Broker: ' + broker, workbook.add_format({'bold': True, 'color': '#d3755c', 'size': 22}))
            worksheet.write(3, 0, 'Overdue Invoices (As of: ' + datetime.now().strftime('%d %b %Y') + ')', workbook.add_format({'bold': True, 'color': '#B72D0F', 'size': 16}))
            worksheet.write(start_row + len_past_due + start_row-1, 0, 'Paid Invoices (As of: ' + datetime.now().strftime('%d %b %Y') + ')', workbook.add_format({'bold': True, 'color': '#108B44', 'size': 16}))

        # If no overdue items, just write all historical paid items.
        else:
            broker_df.to_excel(writer, sheet_name=sheetname, startrow = start_row, index=False)
            worksheet = writer.sheets[sheetname]
            worksheet.write(0, 0, broker + ' - Statement', workbook.add_format({'bold': True, 'color': '#1d9385', 'size': 28}))
            worksheet.write(start_row - 1, 0, 'Paid Invoices (As of: ' + datetime.now().strftime('%d %b %Y') + ')', workbook.add_format({'bold': True, 'color': '#108B44', 'size': 16}))
           
        # Adjust column widths for presentation.
        worksheet.set_column(1, 6, 20)
        worksheet.set_column(0, 0, 30)
        
        # Save / Close writer
        writer.save()

write_broker_reports(invoice_df)